const db = require('../db/index.js');  // 引入数据库模块
const fs = require('fs');  // 引入文件系统模块
const crypto = require('crypto'); // 引入crypto模块，用于生成UUID
const axios = require('axios');

// 上传文件的接口处理函数
exports.uploadFile = (req, res) => {
    // 使用crypto生成唯一标识（UUID）
    const onlyId = crypto.randomUUID()

    // 获取上传文件的临时文件名（由multer等中间件生成）
    let oldName = req.files[0].filename;

    // 处理原始文件名编码转换（解决中文文件名乱码问题）
    // 从latin1编码转换为utf8
    // 当文件名中包含中文时，会出现报错，因为无需转换，此时，将from里的encoding参数设置为utf8即可
    let newName = Buffer.from(req.files[0].originalname, 'latin1').toString('utf8')

    // 将临时文件移动到目标目录并重命名为原始文件名
    // 注意：直接使用原始文件名可能导致文件覆盖
    fs.renameSync('./public/upload/' + oldName, './public/upload/' + newName)

    // 准备SQL插入语句，使用set语法动态设置字段
    const sql = 'insert into bursary_file set ?'

    // 执行数据库插入操作
    db.query(sql, {
        file_url: `http://127.0.0.1:3030/upload/${newName}`,  // 文件访问URL
        onlyId // 绑定生成的UUID
    }, (err, result) => {
        // 如果发生错误，使用自定义错误处理函数返回错误信息
        if (err) return res.cc(err)

        // 返回成功响应，包含唯一标识和图片访问URL
        res.send({
            onlyId,
            status: 0, // 0表示成功
            url: 'http://127.0.0.1:3030/upload/' + newName
        })
    })
}

//文件绑定账号
exports.fileBindAccount = (req, res) => {
    const {apply_id, onlyId} = req.body  // 从请求体中获取账号和文件唯一标识
    const sql1 = 'update bursary_file set apply_id = ? where onlyId in (?)'  // 更新SQL语句
    db.query(sql1, [apply_id, onlyId], (err, result) => {
        if (err) return res.cc(err);
        res.send({
            status: 0,
            message: '绑定成功'
        })
    })
}

//申请助学金
exports.applyBursary = (req, res) => {
    const {
        student_id,
        student_name,
        department,
        major,
        grade,
        mobile,
        family_income,
        student_type,
        bursary_level,
        reason
    } = req.body;  // 获取学生信息

    // 生成编号规则：ZXJ-{Year}{month}-{随机数}
    const now = new Date();
    const year = now.getFullYear();
    const month = String(now.getMonth() + 1).padStart(2, '0');
    const randomNum = Math.floor(Math.random() * 10000).toString().padStart(4, '0');
    const applicationId = `ZXJ-${year}${month}-${randomNum}`;
    const status = 0;  // 初始状态为待审核
    const create_time = new Date();  // 获取当前时间
    const sql2 = 'insert into bursary_apply set ?';  // 插入新申请
    db.query(sql2, {
            id: applicationId,  // 插入生成的编号
            student_id,
            student_name,
            department,
            major,
            grade,
            mobile,
            family_income,
            student_type,
            bursary_level,
            reason,
            status,
            create_time
    }, (err, result) => {
            if (err) return res.cc(err);
            res.send({
                status: 0,
                message: '申请成功',
                applicationId  // 返回生成的编号
            });
        });
};

// 小组审核接口
exports.groupAudit = (req, res) => {
    const {id, status, audit_person_a, audit_opinion_a,type} = req.body;
    let sql;
    if (type === 'Bursary') {
        sql = 'UPDATE bursary_apply SET status = ?, audit_person_a = ?, audit_opinion_a = ?, update_time = NOW() WHERE id = ? AND status = 0';
    }else if(type === 'Scholarship'){
        sql = 'UPDATE scholarship_apply SET status =?, audit_person_a =?, audit_opinion_a =?, update_time = NOW() WHERE id =? AND status = 0';
    }
    if (!audit_opinion_a) {
        return res.send({
            status: 1,
            message: '小组审核必须提交审核意见'
        });
    }
    db.query(sql, [status, audit_person_a, audit_opinion_a, id], (err, result) => {
        if (err) return res.cc(err);
        if (result.affectedRows === 0) {
            return res.send({
                status: 1,
                message: '申请状态不符合小组审核条件或记录不存在'
            });
        }
        res.send({
            status: 0,
            message: '小组审核成功'
        });
    });
};

// 辅导员审核接口
exports.counselorAudit = (req, res) => {
    const {id, status, audit_person_b, audit_opinion_b,type} = req.body;
    let checkSql;
    // 先查询当前状态
    if(type === 'Bursary'){
        checkSql = 'SELECT status FROM bursary_apply WHERE id = ?';
    }else if(type === 'Scholarship'){
        checkSql = 'SELECT status FROM scholarship_apply WHERE id = ?';
    }
    db.query(checkSql, [id], (err, result) => {
        if (err) return res.cc(err);

        // 如果记录不存在
        if (result.length === 0) {
            return res.send({
                status: 1,
                message: '未找到该申请记录'
            });
        }

        // 如果当前状态不是1（小组通过）
        if (result[0].status !== '1') {
            return res.send({
                status: 1,
                message: '该申请尚未通过小组审核，无法进行辅导员审核',
            });
        }

        if (!audit_opinion_b) {
            return res.send({
                status: 1,
                message: '辅导员审核必须提交审核意见'
            });
        }
        let sql;
        if(type === 'Bursary'){
            sql = 'UPDATE bursary_apply SET status =?, audit_person_b =?, audit_opinion_b =?, update_time = NOW() WHERE id =? AND status = 1';
        }else if(type === 'Scholarship'){
            sql = 'UPDATE scholarship_apply SET status =?, audit_person_b =?, audit_opinion_b =?, update_time = NOW() WHERE id =? AND status = 1';
        }
        db.query(sql, [status, audit_person_b, audit_opinion_b, id], (err, result) => {
            if (err) return res.cc(err);
            if (result.affectedRows === 0) {
                return res.send({
                    status: 1,
                    message: '申请状态不符合辅导员审核条件或记录不存在'
                });
            }
            res.send({
                status: 0,
                message: '辅导员审核成功'
            });
        });
    });
};

// 院领导审核接口
exports.deanAudit = (req, res) => {
    const {id, status, audit_person_c, audit_opinion_c, type} = req.body;
    let checkSql;

    // 先查询当前状态
    if(type === 'Bursary'){
        checkSql = 'SELECT status FROM bursary_apply WHERE id = ?';
    }else if(type === 'Scholarship'){
        checkSql = 'SELECT status FROM scholarship_apply WHERE id = ?';
    }

    db.query(checkSql, [id], (err, result) => {
        if (err) return res.cc(err);

        // 如果记录不存在
        if (result.length === 0) {
            return res.send({
                status: 1,
                message: '未找到该申请记录'
            });
        }

        // 如果当前状态不是2（辅导员通过）
        if (result[0].status !== '2') {
            return res.send({
                status: 1,
                message: '该申请尚未通过辅导员审核，无法进行院领导审核',
            });
        }

        if (!audit_opinion_c) {
            return res.send({
                status: 1,
                message: '院领导审核必须提交审核意见'
            });
        }

        let sql;
        if(type === 'Bursary'){
            sql = 'UPDATE bursary_apply SET status =?, audit_person_c =?, audit_opinion_c =?, update_time = NOW() WHERE id =? AND status = 2';
        }else if(type === 'Scholarship'){
            sql = 'UPDATE scholarship_apply SET status =?, audit_person_c =?, audit_opinion_c =?, update_time = NOW() WHERE id =? AND status = 2';
        }

        db.query(sql, [status, audit_person_c, audit_opinion_c, id], (err, result) => {
            if (err) return res.cc(err);
            if (result.affectedRows === 0) {
                return res.send({
                    status: 1,
                    message: '申请状态不符合院领导审核条件或记录不存在'
                });
            }
            res.send({
                status: 0,
                message: '院领导审核成功'
            });
        });
    });
};


// 监听分页查询个人助学金申请列表接口
exports.getBursaryApplications = (req, res) => {
    const {student_id, page} = req.body;  // 从请求体中获取学生ID和页码
    const pageSize = 10; // 每页显示10条记录
    const offset = (page - 1) * pageSize; // 计算偏移量

    // SQL查询语句
    const sql = 'select * from bursary_apply where student_id = ? limit ? offset ?';
    const countSql = 'select count(*) as total from bursary_apply where student_id = ?';

    // 先查询总数
    db.query(countSql, [student_id], (err, countResult) => {
        if (err) return res.cc(err);

        // 查询分页数据
        db.query(sql, [student_id, pageSize, offset], (err, result) => {
            if (err) return res.cc(err);

            // 返回分页结果
            res.send({
                status: 0,
                data: result,
                total: countResult[0].total,
                pageSize,
                currentPage: page
            });
        });
    });
};



// 更新助学金申请信息接口
exports.updateBursaryApplication = (req, res) => {
    const {id,family_income,bursary_level, reason} = req.body;
    const sql = 'update bursary_apply set family_income = ?, bursary_level = ?, reason = ?, update_time = NOW() where id = ? and status = 0';
    db.query(sql, [family_income, bursary_level, reason, id], (err, result) => {
        if (err) return res.cc(err);
        if (result.affectedRows === 0) {
            return res.send({
                status: 1,
                message: '申请已审核，无法修改'
            });
        }
        res.send({
            status: 0,
            message: '申请信息更新成功'
        });
    });
};

//监听分页查询助学金申请列表
exports.getBursaryByPage = (req, res) => {
    const {page} = req.body;
    const pageSize = 10; // 可配置的分页大小
    const offset = (page - 1) * pageSize;

    // 查询总数
    const countSql = 'select count(*) as total from bursary_apply';
    const dataSql = 'select * from bursary_apply limit ? offset ?';

    // 先查询总数
    db.query(countSql, (err, countResult) => {
        if (err) return res.cc(err);

        // 查询分页数据
        db.query(dataSql, [pageSize, offset], (err, result) => {
            if (err) return res.cc(err);

            res.send({
                status: 0,
                data: result,
                total: countResult[0].total,
                pageSize,
                currentPage: page
            });
        });
    });
}


// 根据编号获取申请表详情接口
exports.getBursaryById = (req, res) => {
    const { id } = req.body;  // 从请求体中获取申请编号

    // 查询SQL语句
    const sql = 'SELECT * FROM bursary_apply WHERE id = ?';

    // 执行数据库查询
    db.query(sql, [id], (err, result) => {
        if (err) return res.cc(err);  // 如果出错，返回错误信息

        // 如果查询结果为空
        if (result.length === 0) {
            return res.send({
                status: 1,  // 失败状态码
                message: '未找到该申请记录'  // 错误提示信息
            });
        }

        // 返回查询结果
        res.send({
            status: 0,  // 成功状态码
            data: result  // 返回第一条查询结果
        });
    });
};

//查询助学金申请信息返回文件链接
exports.getBursaryFileUrl = (req, res) => {
    const {apply_id} = req.body;  // 从请求体中获取学生ID
    const sql = 'select file_url from bursary_file where apply_id =?';
    db.query(sql, apply_id, (err, result) => {
        if (err) return res.cc(err);  // 如果出错，返回错误信息
        res.send({
            status: 0,  // 成功状态码
            data: result  // 返回查询结果
        });
    })
}

//申请奖学金
exports.applyScholarship = (req, res) => {
    const {
        student_id,
        student_name,
        department,
        major,
        grade,
        mobile,
        scholarship_level,
        reason
    } = req.body;  // 获取学生信息

    // 生成编号规则：JXJ-{Year}{month}-{随机数}
    const now = new Date();
    const year = now.getFullYear();
    const month = String(now.getMonth() + 1).padStart(2, '0');
    const randomNum = Math.floor(Math.random() * 10000).toString().padStart(4, '0');
    const applicationId = `JXJ-${year}${month}-${randomNum}`;

    const sql = 'select * from scholarship_apply where student_id = ?';  // 查询是否已申请
    db.query(sql, student_id, (err, result) => {
        if (err) return res.cc(err);
        const status = 0;  // 初始状态为待审核
        const create_time = new Date();  // 获取当前时间
        const sql2 = 'insert into scholarship_apply set ?';  // 插入新申请
        db.query(sql2, {
            id: applicationId,  // 插入生成的编号
            student_id,
            student_name,
            department,
            major,
            grade,
            mobile,
            scholarship_level,
            reason,
            status,
            create_time
        }, (err, result) => {
            if (err) return res.cc(err);
            res.send({
                status: 0,
                message: '申请成功',
                applicationId  // 返回生成的编号
            });
        });
    });
};

//监听分页查询个人奖学金申请列表
exports.getScholarshipByPage = (req, res) => {
    const {page} = req.body;
    const pageSize = 10; // 可配置的分页大小
    const offset = (page - 1) * pageSize;
    // 查询总数
    const countSql ='select count(*) as total from scholarship_apply';
    const dataSql = 'select * from scholarship_apply limit ? offset ?';
    // 先查询总数
    db.query(countSql, (err, countResult) => {
        if (err) return res.cc(err);
        // 查询分页数据
        db.query(dataSql, [pageSize, offset], (err, result) => {
            if (err) return res.cc(err);
            res.send({
                status: 0,
                data: result,
                total: countResult[0].total,
                pageSize,
                currentPage: page
            });
        })
    })
}

// 根据编号获取申请表详情接口
exports.getScholarById = (req, res) => {
    const { id } = req.body;  // 从请求体中获取申请编号

    // 查询SQL语句
    const sql = 'SELECT * FROM scholarship_apply WHERE id = ?';

    // 执行数据库查询
    db.query(sql, [id], (err, result) => {
        if (err) return res.cc(err);  // 如果出错，返回错误信息

        // 如果查询结果为空
        if (result.length === 0) {
            return res.send({
                status: 1,  // 失败状态码
                message: '未找到该申请记录'  // 错误提示信息
            });
        }

        // 返回查询结果
        res.send({
            status: 0,  // 成功状态码
            data: result
        });
    });
};

// 监听分页查询奖学金申请列表接口
exports.getScholarshipApplications = (req, res) => {
    const {student_id, page} = req.body;  // 从请求体中获取学生ID和页码
    const pageSize = 10; // 每页显示10条记录
    const offset = (page - 1) * pageSize; // 计算偏移量

    const sql = 'select * from scholarship_apply where student_id = ? limit ? offset ?';
    const countSql = 'select count(*) as total from scholarship_apply where student_id = ?';
    // 先查询总数
    db.query(countSql, student_id ? [student_id] : [], (err, countResult) => {
        if (err) return res.cc(err);

        // 查询分页数据
        db.query(sql, student_id ? [student_id, pageSize, offset] : [pageSize, offset], (err, result) => {
            if (err) return res.cc(err);

            res.send({
                status: 0,
                data: result,
                total: countResult[0].total,
                pageSize,
                currentPage: page
            });
        });
    });
}

// 更新奖学金申请信息接口
exports.updateScholarshipApplication = (req, res) => {
    const {id,scholarship_level, reason} = req.body;
    const sql = 'update scholarship_apply set  scholarship_level = ?, reason = ?, update_time = NOW() where id = ? and status = 0';
    db.query(sql, [scholarship_level, reason, id], (err, result) => {
        if (err) return res.cc(err);
        if (result.affectedRows === 0) {
            return res.send({
                status: 1,
                message: '申请已审核，无法修改'
            });
        }
        res.send({
            status: 0,
            message: '申请信息更新成功'
        });
    });
};

//删除指定的助学金申请
exports.deleteBursaryApplication = (req, res) => {
    const {id} = req.body;
    const sql1 = 'delete from bursary_apply where id =?';
    const sql2 = 'delete from bursary_file where apply_id =?';

    // 先删除申请记录
    db.query(sql1, id, (err, result) => {
        if (err) return res.cc(err);
        if (result.affectedRows === 0) {
            return res.send({
                status: 1,
                message: '删除失败'
            });
        }

        // 再删除对应的文件记录
        db.query(sql2, id, (err, result) => {
            if (err) return res.cc(err);
            res.send({
                status: 0,
                message: '删除成功'
            });
        });
    });
};

//删除指定的奖学金申请
exports.deleteScholarshipApplication = (req, res) => {
    const {id} = req.body;
    const sql = 'delete from scholarship_apply where id =?';
    db.query(sql, id, (err, result) => {
        if (err) return res.cc(err);
        if (result.affectedRows === 0) {
            return res.send({
                status: 1,
                message: '删除失败'
            })
        }
        res.send({
            status: 0,
            message: '删除成功'
        })
    })
}
// 创建axios实例并配置
const axiosInstance = axios.create({
    baseURL: 'https://106.ihuyi.com',
    timeout: 10000, // 设置10秒超时
    headers: {
        'Content-Type': 'application/x-www-form-urlencoded'
    }
});

// 短信请求参数
const APIID = 'C55231404';
const APIKEY = 'dda631dbe3e99c1739608956811b7dc2';

// 修改短信发送函数
exports.sendVerificationCode = async (req, res) => {
    const { mobile } = req.body; // 从请求体中获取手机号
    if (!mobile) {
        return res.send({
            status: 1,
            message: '手机号不能为空'
        });
    }

    const content = '您的验证码是：1234。请不要把验证码泄露给其他人。'

        const result = await axiosInstance.post('/webservice/sms.php?method=Submit', {
            account: APIID,
            password: APIKEY,
            mobile: mobile,
            content: content,
            format: 'json'
        });
        // 根据接口返回结果判断是否发送成功
    if(result.data.code == 2){
        return res.send({
            status: 0,
            message: '验证码发送成功'
        });
    }else{
        return res.send({
            status: 1,
            message: '验证码发送失败'
        });
    }
};